﻿using BCCommon;
using BCEntity.Common.News.NewsContentComment;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;

namespace BCData.Common.News.NewsContentComment
{
    public class NewsContentCommentData : INewsContentCommentData
    {
        private const string QUERY_EXISTS = @"SELECT 1 FROM newscontentcomment";
        private const string QUERY = @"SELECT A.NewsContentCommentId,A.NewsContentId,B.Title,A.Comment,A.CreateTime,A.ParentCommentId,A.IsAnonymous,A.TreeCode,
                                            A.UserId,A.UserName,A.LikeCount,A.IsDelete,A.Enabled
                                            FROM newscontentcomment A
                                                INNER JOIN newscontent B ON A.NewsContentId=B.NewsContentId";
        private const string sqlbyID = @"SELECT A.NewsContentCommentId,A.NewsContentId,B.Title,A.Comment,A.CreateTime,A.IsAnonymous,A.UserId,A.UserName,
                                                A.ParentCommentId,A.TreeCode,A.LikeCount,A.IsDelete,A.Enabled,
                                            (SELECT COUNT(1) FROM newscontentcomment WHERE FIND_IN_SET(NewsContentCommentId, queryChildrenCommentInfo(A.NewsContentCommentId))) AS ChildrenCommentCount
                                            FROM newscontentcomment A
                                                INNER JOIN newscontent B ON A.NewsContentId=B.NewsContentId
                                            WHERE A.NewsContentCommentId=?NewsContentCommentId";

        protected readonly IDatabaseContext databaseContext;

        public NewsContentCommentData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        #region 检查新闻内容评论是否存在
        public async Task<bool> Exists(long newsContentCommentId)
        {
            string sql = string.Format(@"{0} WHERE NewsContentCommentId=?NewsContentCommentId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentCommentId", newsContentCommentId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public NewsContentCommentEntity Add(NewsContentCommentEntity entity)
        {
            string sql = @"INSERT INTO NewsContentComment(NewsContentId,`Comment`,CreateTime,ParentCommentId,IsAnonymous,UserId,UserName,TreeCode)
                            VALUES(?NewsContentId,?ContentComment,?CreateTime,?ParentCommentId,?IsAnonymous,?UserId,?UserName,?TreeCode);
                            SELECT NewsContentCommentId,NewsContentId,`Comment`,CreateTime,ParentCommentId,IsAnonymous,UserId,UserName,TreeCode,LikeCount,IsDelete
                            FROM NewsContentComment 
                            WHERE NewsContentCommentId=LAST_INSERT_ID();";
            string treeCode = string.Empty;
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", entity.NewsContentId, DbType.Int64);
            parameters.Add("?ContentComment", entity.Comment, DbType.AnsiString, size: 500);
            parameters.Add("?ParentCommentId", entity.ParentCommentId, DbType.Int64);
            parameters.Add("?CreateTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?IsAnonymous", entity.IsAnonymous, DbType.Boolean);
            parameters.Add("?UserId", entity.UserId, DbType.Int32);
            parameters.Add("?UserName", entity.UserName, DbType.AnsiString, size: 20);
            parameters.Add("?CommentId", entity.ParentCommentId, DbType.Int64, ParameterDirection.Input);
            parameters.Add("?Code", string.Empty, DbType.AnsiString, ParameterDirection.ReturnValue, size: 1000);
            this.databaseContext.Master.Execute("QueryParentCommentIdPathInfo", parameters, databaseContext.Transaction, commandType: CommandType.StoredProcedure);
            if (!string.IsNullOrWhiteSpace(parameters.Get<string>("@Code")))
            {
                parameters.Add("?TreeCode", parameters.Get<string>("@Code"), DbType.AnsiString, size: 1000);
            }
            else
            {
                parameters.Add("?TreeCode", "0", DbType.AnsiString, size: 1000);
            }
            return this.databaseContext.Master.QueryFirstOrDefault<NewsContentCommentEntity>(sql, parameters);
        }

        /// <summary>
        /// 获取所有新闻内容评论列表（分页）
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="dataCount">总页数</param>
        /// <returns></returns>
        public IEnumerable<NewsContentCommentEntity> GetAllNewsContentComments(string title, string comment, DateTime? start, DateTime? end, out int dataCount, bool? ascending, int pageIndex, int pageSize)
        {
            string sql = @"SELECT A.NewsContentCommentId,A.NewsContentId,B.Title,A.Comment,A.CreateTime,A.IsAnonymous,A.UserId,A.UserName,
                                  A.ParentCommentId,A.TreeCode,A.LikeCount,A.IsDelete,A.Enabled,
		                            CASE WHEN parent.CommentCount IS NOT NULL THEN parent.CommentCount
		                            ELSE 0 END AS ChildrenCommentCount
                            FROM newscontentcomment A 
                            LEFT JOIN (
			                            SELECT  ParentCommentId,NewsContentCommentId,
				                            CASE WHEN COUNT(1) > 0 THEN COUNT(1)
					                            ELSE 0 END AS CommentCount
				                            FROM NewsContentComment
				                            WHERE ParentCommentId IS NOT NULL AND ParentCommentId <> 0 GROUP BY ParentCommentId
                                      ) AS parent ON A.NewsContentCommentId = parent.ParentCommentId
		                    INNER JOIN newscontent B ON A.NewsContentId=B.NewsContentId";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.Enabled=1");
            if (!string.IsNullOrWhiteSpace(title))
            {
                wheres.Add("B.Title LIKE CONCAT('%',?Title,'%')");
                parameters.Add("?Title", title, DbType.AnsiString, size: 50);
            }
            if (!string.IsNullOrWhiteSpace(comment))
            {
                wheres.Add("A.Comment LIKE CONCAT('%',?Comment,'%')");
                parameters.Add("?Comment", comment, DbType.AnsiString, size: 500);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start AND A.CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }

            string newsql = string.Format(@"{0} {1} ORDER BY A.CreateTime {4},A.LikeCount {4} LIMIT {2}, {3}; 
                                            SELECT COUNT(1) 
                                            FROM NewsContentComment A 
                                            INNER JOIN newscontent B ON A.NewsContentId=B.NewsContentId {1}",
                sql,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize,
                 ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            var results = this.databaseContext.Slave.QueryMultiple<NewsContentCommentEntity, int>(newsql, parameters);

            dataCount = results.Item2;

            return results.Item1;

        }
        /// <summary>
        /// 获取新闻内容评论列表（分页）
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="dataCount">总页数</param>
        /// <returns></returns>
        public IEnumerable<NewsContentCommentEntity> GetPageableList(long newsContentId, int? userId, int pageIndex, int pageSize, out int dataCount)
        {
            string sql = @"SELECT A.NewsContentCommentId,A.NewsContentId,B.Title,A.Comment,A.CreateTime,A.IsAnonymous,A.UserId,A.UserName,A.ParentCommentId,TreeCode,A.LikeCount,A.IsDelete,A.Enabled,
                    (
                    SELECT CASE WHEN COUNT(1)>0 THEN COUNT(1) ELSE 0 END
                        FROM newscontentcomment 
                    WHERE ParentCommentId=A.NewsContentCommentId AND NewsContentId=?newsContentId
                    ) AS ChildrenCommentCount,
                    C.Avatar as UserAvatar
                    FROM newscontentcomment A 
                        INNER JOIN newscontent B ON A.NewsContentId=B.NewsContentId
                        Left JOIN User C ON A.UserId=C.UserId ";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.Enabled=1");
            if (newsContentId > 0)
            {
                wheres.Add("A.NewsContentId=?newsContentId");
                parameters.Add("?newsContentId", newsContentId, DbType.Int64);
            }
            if (userId.HasValue)
            {
                wheres.Add("A.UserId=?UserId");
                parameters.Add("?UserId", userId.Value, DbType.Int32, size: 10);
            }
            wheres.Add("A.ParentCommentId=?ParentCommentId");
            parameters.Add("?ParentCommentId", 0, DbType.Int64);
            string newsql = string.Format("{0} {1} ORDER BY A.LikeCount DESC LIMIT {2}, {3}; SELECT COUNT(1) FROM NewsContentComment A {1}",
                sql,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize);

            var results = this.databaseContext.Slave.QueryMultiple<NewsContentCommentEntity, int>(newsql, parameters);

            dataCount = results.Item2;

            return results.Item1;
        }
        /// <summary>
        /// 获取回复
        /// </summary>
        /// <param name="NewsContentCommentId"></param>
        /// <returns></returns>
        public IEnumerable<NewsContentCommentEntity> GetChildrenCommentByCommentId(long newsContentId, long newsContentCommentId, int pageIndex, int pageSize, out int dataCount)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.Enabled=1");
            wheres.Add("A.NewsContentId=?NewsContentId");
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);

            wheres.Add("A.ParentCommentId=?ParentCommentId");
            parameters.Add("?ParentCommentId", newsContentCommentId, DbType.Int64);

            string newsql = string.Format(@"{0} {1} ORDER BY A.CreateTime DESC LIMIT {2},{3}; SELECT COUNT(1) FROM newscontentcomment A {1}",
                 @"SELECT A.NewsContentCommentId,A.NewsContentId,B.Title,A.Comment,A.CreateTime,A.IsAnonymous,A.UserId,A.UserName,A.ParentCommentId,A.TreeCode,A.LikeCount,A.IsDelete,
                    (SELECT CASE WHEN COUNT(1)>0 THEN COUNT(1) ELSE 0 END
                    FROM newscontentcomment 
                    WHERE ParentCommentId=A.NewsContentCommentId AND NewsContentId=?newsContentId) AS ChildrenCommentCount
                    FROM newscontentcomment A
                        INNER JOIN newscontent B ON A.NewsContentId=B.NewsContentId",
                wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty,
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize);
            var results = this.databaseContext.Slave.QueryMultiple<NewsContentCommentEntity, int>(newsql, parameters);

            dataCount = results.Item2;

            return results.Item1;
        }
        /// <summary>
        /// 通过ID获取一条数据
        /// </summary>
        /// <param name="newsContentId">新闻内容评论ID</param>
        /// <returns></returns>
        public NewsContentCommentEntity Get(long newsContentCommentId)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentCommentId", newsContentCommentId, DbType.Int64);
            return this.databaseContext.Slave.QueryFirstOrDefault<NewsContentCommentEntity>(sqlbyID, parameters);
        }

        /// <summary>
        /// 根据文章编号获取所属评价信息列表
        /// </summary>
        /// <param name="newsContentId"></param>
        /// <returns></returns>
        public IEnumerable<NewsContentCommentEntity> GetNewsContentCommentByNewsContentId(long newsContentId)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            return this.databaseContext.Slave.Query<NewsContentCommentEntity>(string.Format("{0} WHERE A.NewsContentId=?NewsContentId AND A.Enabled=1;", QUERY), parameters);
        }

        /// <summary>
        /// 根据文章编号删除下面所属的评价信息
        /// </summary>
        /// <param name="newsContentId">文章编号</param>
        /// <returns></returns>
        public bool DeleteNewsContentCommentByNewsContentId(long newsContentId)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentId", newsContentId, DbType.Int64);
            var result = this.databaseContext.Master.Execute("UPDATE NewsContentComment SET IsDelete=1 WHERE NewsContentId=?NewsContentId;", parameters, databaseContext.Transaction);
            return result > 0;
        }

        public NewsContentCommentEntity UpdateCommentLikeCount(long newsContentCommentId, bool isAdd)
        {
            StringBuilder sql = new StringBuilder("UPDATE NewsContentComment SET ");
            if (isAdd)
            {
                sql.AppendLine("LikeCount=LikeCount+1");
            }
            else
            {
                sql.AppendLine("LikeCount=LikeCount-1");
            }
            sql.AppendLine("WHERE NewsContentCommentId=?NewsContentCommentId;");
            sql.AppendLine(string.Format("{0} WHERE A.NewsContentCommentId=?NewsContentCommentId;", QUERY));

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentCommentId", newsContentCommentId, DbType.Int64, size: 20);
            return this.databaseContext.Master.QueryFirst<NewsContentCommentEntity>(sql.ToString(), parameters, databaseContext.Transaction);
        }

        public void Delete(long newsContentCommentId)
        {
            string sql = @"UPDATE NewsContentComment SET
                                IsDelete=1
                            WHERE NewsContentCommentId=?NewsContentCommentId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentCommentId", newsContentCommentId, DbType.Int64, size: 20);
            this.databaseContext.Master.Execute(sql.ToString(), parameters, databaseContext.Transaction);
        }

        /// <summary>
        /// 分页获取新闻所有评论列表
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="dataCount">总页数</param>
        /// <returns></returns>
        public IEnumerable<NewsContentCommentEntity> GetPageableListNewsContentComments(long newsContentId, int? userId, int pageIndex, int pageSize, out int dataCount)
        {
            string sql = @"SELECT A.NewsContentCommentId,A.NewsContentId,B.Title,A.Comment,A.CreateTime,A.IsAnonymous,A.UserId,A.UserName,A.ParentCommentId,A.TreeCode,A.LikeCount,A.IsDelete,
                    (
                    SELECT CASE WHEN COUNT(1)>0 THEN COUNT(1) ELSE 0 END
                        FROM newscontentcomment 
                    WHERE ParentCommentId=A.NewsContentCommentId AND NewsContentId=?newsContentId
                    ) AS ChildrenCommentCount,
                    C.Avatar as UserAvatar,
                    Parent.`Comment` as ParentComment,
                    Parent.LikeCount as ParentLikeCount,
                    Parent.UserName as ParentUserName,
                    A.Enabled
                    FROM newscontentcomment A 
                    INNER JOIN newscontent B ON A.NewsContentId=B.NewsContentId
                    Left JOIN User C ON A.UserId=C.UserId 
                    Left JOIN newscontentcomment Parent ON Parent.NewsContentCommentId=A.ParentCommentId";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.Enabled=1");
            if (newsContentId > 0)
            {
                wheres.Add("A.NewsContentId=?newsContentId");
                parameters.Add("?newsContentId", newsContentId, DbType.Int64);
            }
            if (userId.HasValue)
            {
                wheres.Add("A.UserId=?UserId");
                parameters.Add("?UserId", userId.Value, DbType.Int32, size: 10);
            }
            string newsql = string.Format("{0} {1} ORDER BY A.LikeCount DESC LIMIT {2}, {3}; SELECT COUNT(1) FROM NewsContentComment A {1}",
                sql,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize);

            var results = this.databaseContext.Slave.QueryMultiple<NewsContentCommentEntity, int>(newsql, parameters);

            dataCount = results.Item2;

            return results.Item1;
        }

        #region 启用新闻内容评论
        public async Task<bool> Enabled(long newsContentCommentId)
        {
            string sql = @"UPDATE newscontentcomment SET Enabled=1 WHERE NewsContentCommentId=?NewsContentCommentId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentCommentId", newsContentCommentId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 禁用新闻内容评论
        public async Task<bool> Disabled(long newsContentCommentId)
        {
            string sql = @"UPDATE newscontentcomment SET Enabled=0 WHERE NewsContentCommentId=?NewsContentCommentId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?NewsContentCommentId", newsContentCommentId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion
    }
}
